﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using CSharp.Libs.WPF;
using CSharp.Libs.Misc;
using System.IO;
using System.Windows.Media;
using CSharp.Libs.Database;
using System.Windows.Input;
using System.Windows.Forms;
using System.Collections.ObjectModel;
using System.Data.SQLite;
using System.Data;



namespace SQLiteStatementGenerator
{
    class MainWindowViewModel : ViewModelBase
    {
        #region declarations
        private LocalApplicationOptions _LAO = new LocalApplicationOptions();
        private DatabaseOperationsSQLite _DBOps = null;

        private ObservableCollection<String> _ocTables = new ObservableCollection<String>();
        private RelayCommand _rcOpenDatabase = null;
        private RelayCommand _rcCreateInsertStatement = null;
        private RelayCommand _rcCreateUpdateStatement = null;

        private String _strDatabaseFileName = "";
        private String _strSelectedTable = "";
        private String _strInsertStatementTemplate = "";
        private String _strUpdateStatementTemplate = "";
        private String _strResult = "";

        private const String LAO_DATABASE_FILE_NAME = "DatabaseFilename";
        private const String TMPL_PLACEHOLDER_TABLE_NAME = "%TABLENAME%";
        private const String TMPL_PLACEHOLDER_INSERT_COLUMN_NAMES = "%INSERTCOLUMNNAMES%";
        private const String TMPL_PLACEHOLDER_INSERT_VALUES = "%INSERTVALUES%";
        private const String TMPL_PLACEHOLDER_UPDATE_SETS = "%UPDATESETS%";
        #endregion



        #region constructor / terminator
        public MainWindowViewModel()
        {
            _strDatabaseFileName = _LAO.GetOption(LAO_DATABASE_FILE_NAME);
            _DBOps = new DatabaseOperationsSQLite(_strDatabaseFileName);
            _GetAllTables();

            _strUpdateStatementTemplate = String.Concat(
                "String strDestinationVariable = String.Concat(", Environment.NewLine, "\"UPDATE ", TMPL_PLACEHOLDER_TABLE_NAME, " SET \", ", TMPL_PLACEHOLDER_UPDATE_SETS, Environment.NewLine, "\";\");");
            _strInsertStatementTemplate = String.Concat(
                "String strDestinationVariable = String.Concat(", Environment.NewLine, "\"INSERT INTO ", TMPL_PLACEHOLDER_TABLE_NAME, "(\", ", TMPL_PLACEHOLDER_INSERT_COLUMN_NAMES, Environment.NewLine, "\") VALUES (\", ", TMPL_PLACEHOLDER_INSERT_VALUES, Environment.NewLine, "\");\");");
        }

        ~MainWindowViewModel()
        {
            //if closed with a not existing filename (i.e. _strDatabaseFileName == ""), delete value in option file.
            if (!File.Exists(_strDatabaseFileName))
            {
                _LAO.SetOption2(LAO_DATABASE_FILE_NAME, "");
            }

            _DBOps = null;
        }
        #endregion



        #region private functions
        /// <summary>_OpenDatabase</summary>
        /// <history>17.05.2011</history>
        private void _OpenDatabase()
        {
            try
            {
                OpenFileDialog OFD = new OpenFileDialog();
                OFD.CheckFileExists = true;
                OFD.CheckPathExists = true;
                OFD.DefaultExt = "*.sqlite";
                OFD.Filter = "SQLite (*.sqlite;*.s3db)|*.sqlite;*.s3db|All Files (*.*)|*.*";
                OFD.Multiselect = false;
                if (File.Exists(_strDatabaseFileName))
                {
                    OFD.InitialDirectory = new FileInfo(_strDatabaseFileName).Directory.ToString();
                }
                if (OFD.ShowDialog() == System.Windows.Forms.DialogResult.OK)
                {
                    DataBaseFileName = OFD.FileName;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>_GetAllTables</summary>
        /// <history>17.05.2011</history>
        private void _GetAllTables()
        {
            try
            {
                if (_DBOps.DatabaseOpened)
                {
                    String strSQL = "SELECT name FROM sqlite_master WHERE type='table' ORDER BY name;";
                    DataTable DT = _DBOps.GetDataTable(strSQL);

                    if (DT != null &&
                        DT.Rows.Count > 0)
                    {
                        _ocTables.Clear();

                        foreach (DataRow drName in DT.Rows)
                        {
                            _ocTables.Add(drName["name"].ToString());
                        }

                        OnPropertyChanged("Tables");
                    }
                }
                else
                {
                    _ocTables.Clear();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>_CreateInsertStatement</summary>
        /// <history>18.05.2011</history>
        private void _CreateInsertStatement()
        {
            try
            {
                if (_strSelectedTable != "")
                {
                    String strSQL = "SELECT * FROM sqlite_master WHERE name='" + _strSelectedTable + "'";

                    if (_DBOps.HasRows(strSQL))
                    {
                        strSQL = "SELECT * FROM " + _strSelectedTable;
                        DataTable dtTable = _DBOps.GetDataTable(strSQL);

                        if (dtTable.Columns.Count > 0)
                        {
                            String strInsertColumnNames = "";
                            String strInsertValues = "";

                            foreach (DataColumn DC in dtTable.Columns)
                            {
                                //MessageBox.Show(DC.ColumnName, "Column Names");
                                strInsertColumnNames = String.Concat(strInsertColumnNames, Environment.NewLine, "\"", DC.ColumnName, ", \", ");

                                if (DC.DataType == System.Type.GetType("System.String") ||
                                    DC.DataType == System.Type.GetType("System.Char") ||
                                    DC.DataType == System.Type.GetType("System.DateTime"))
                                {
                                    strInsertValues = String.Concat(strInsertValues, Environment.NewLine, "\"'\", Variable_", DC.ColumnName, ", \"', \", ");
                                }
                                else
                                {
                                    strInsertValues = String.Concat(strInsertValues, Environment.NewLine, "Variable_", DC.ColumnName, ".ToString(), \", \", ");
                                }
                            }

                            strInsertColumnNames = strInsertColumnNames.Substring(0, strInsertColumnNames.Length - 5) + "\",";
                            strInsertValues = strInsertValues.Substring(0, strInsertValues.Length - 5) + "\",";

                            _strResult = _strInsertStatementTemplate;
                            _strResult = _strResult.Replace(TMPL_PLACEHOLDER_TABLE_NAME, _strSelectedTable);
                            _strResult = _strResult.Replace(TMPL_PLACEHOLDER_INSERT_COLUMN_NAMES, strInsertColumnNames);
                            _strResult = _strResult.Replace(TMPL_PLACEHOLDER_INSERT_VALUES, strInsertValues);

                            OnPropertyChanged("Result");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        /// <summary>_CreateInsertStatement</summary>
        /// <history>18.05.2011</history>
        private void _CreateUpdateStatement()
        {
            try
            {
                if (_strSelectedTable != "")
                {
                    String strSQL = "SELECT * FROM sqlite_master WHERE name='" + _strSelectedTable + "'";

                    if (_DBOps.HasRows(strSQL))
                    {
                        strSQL = "SELECT * FROM " + _strSelectedTable;
                        DataTable dtTable = _DBOps.GetDataTable(strSQL);

                        if (dtTable.Columns.Count > 0)
                        {
                            String strUpdate = "";

                            foreach (DataColumn DC in dtTable.Columns)
                            {
                                //MessageBox.Show(DC.ColumnName, "Column Names");
                                

                                if (DC.DataType == System.Type.GetType("System.String") ||
                                    DC.DataType == System.Type.GetType("System.Char") ||
                                    DC.DataType == System.Type.GetType("System.DateTime"))
                                {
                                    strUpdate = String.Concat(strUpdate, Environment.NewLine, 
                                        "\"", DC.ColumnName, " = '\", ",
                                        "Variable_", DC.ColumnName, ", \"', \", ");
                                }
                                else
                                {
                                    strUpdate = String.Concat(strUpdate, Environment.NewLine, 
                                        "\"", DC.ColumnName, " = \", ",
                                        "Variable_", DC.ColumnName, ".ToString(), \", \", ");
                                }
                            }

                            strUpdate = strUpdate.Substring(0, strUpdate.Length - 5) + "\",";

                            _strResult = _strUpdateStatementTemplate;
                            _strResult = _strResult.Replace(TMPL_PLACEHOLDER_TABLE_NAME, _strSelectedTable);
                            _strResult = _strResult.Replace(TMPL_PLACEHOLDER_UPDATE_SETS, strUpdate);

                            OnPropertyChanged("Result");
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }
        #endregion



        #region interface
        #region properties
        public ICommand OpenDatabase
        {
            get
            {
                if (_rcOpenDatabase == null)
                {
                    _rcOpenDatabase = new RelayCommand(
                        param => this._OpenDatabase(),
                        param => this.CanOpenDatabase);
                }
                return _rcOpenDatabase;
            }
        }
        public Boolean CanOpenDatabase
        {
            get
            {
                return true;
            }
        }

        public ICommand CreateInsertStatement
        {
            get
            {
                if (_rcCreateInsertStatement == null)
                {
                    //_rcCreateInsertStatement = new RelayCommand(_CreateInsertStatement, CanCreateInsertStatement);

                    _rcCreateInsertStatement = new RelayCommand(
                        param => this._CreateInsertStatement(),
                        param => this.CanCreateInsertStatement);
                }
                return _rcCreateInsertStatement;
            }
        }
        public Boolean CanCreateInsertStatement
        {
            get
            {
                return true;
            }
        }

        public ICommand CreateUpdateStatement
        {
            get
            {
                if (_rcCreateUpdateStatement == null)
                {
                    //_rcCreateInsertStatement = new RelayCommand(_CreateInsertStatement, CanCreateInsertStatement);

                    _rcCreateUpdateStatement = new RelayCommand(
                        param => this._CreateUpdateStatement(),
                        param => this.CanCreateUpdateStatement);
                }
                return _rcCreateUpdateStatement;
            }
        }
        public Boolean CanCreateUpdateStatement
        {
            get
            {
                return true;
            }
        }

        public String DataBaseFileName
        {
            get
            {
                return _strDatabaseFileName;
            }
            set
            {
                _strDatabaseFileName = value;

                if (File.Exists(_strDatabaseFileName))
                {
                    _LAO.SetOption2(LAO_DATABASE_FILE_NAME, _strDatabaseFileName);
                }

                _DBOps.DatabaseFileName = _strDatabaseFileName;
                _GetAllTables();

                OnPropertyChanged("DataBaseFileName");
                OnPropertyChanged("DatabaseOpened");
                OnPropertyChanged("DatabaseOpenedBrush");
            }
        }

        public Boolean DatabaseOpened
        {
            get
            {
                return _DBOps.DatabaseOpened;
            }
        }

        public SolidColorBrush DatabaseOpenedBrush
        {
            get
            {
                if (_DBOps.DatabaseOpened)
                {
                    return Brushes.Green;
                }
                else
                {
                    return Brushes.Red;
                }
            }
        }

        public ObservableCollection<String> Tables
        {
            get
            {
                return _ocTables;
            }
        }

        public String SelectedTable
        {
            get
            {
                return _strSelectedTable;
            }
            set
            {
                _strSelectedTable = value;
            }
        }

        public String Result
        {
            get
            {
                return _strResult;
            }
        }
        #endregion

        #region methods
        #endregion
        #endregion
    }
}
